|
In relational databases, the Log trigger or History trigger is a mechanism for automatic recording of information about changes inserting or/and updating or/and deleting rows in a database table. It is a particular technique for change data capturing, and in data warehousing for dealing with slowly changing dimensions. == Definition == Suppose there is a table which we want to audit. This table contains the following columns: Column1, Column2, ..., ColumnnThe column Column1 is assumed to be the primary key.These columns are defined to have the following types: Type1, Type2, ..., TypenThe Log Trigger works writing the changes (INSERT, UPDATE and DELETE operations) on the table in another, history table, defined as following: CREATE TABLE HistoryTable ( Column1 Type1, Column2 Type2, : : Columnn Typen, StartDate DATETIME, EndDate DATETIME ) As shown above, this new table contains the same columns as the original table, and additionally two new columns of type DATETIME: StartDate and EndDate. This is known as tuple versioning. These two additional columns define a period of time of "validity" of the data associated with a specified entity (the entity of the primary key), or in other words, it stores how the data were in the period of time between the StartDate (included) and EndDate (not included).For each entity (distinct primary key) on the original table, the following structure is created in the history table. Data is shown as example. Notice that if they are shown chronologically the EndDate column of any row is exactly the StartDate of its successor (if any). It does not mean that both rows are common to that point in time, since -by definition- the value of EndDate is not included.There are two variants of the Log trigger, depending how the old values (DELETE, UPDATE) and new values (INSERT, UPDATE) are exposed to the trigger (it is RDBMS dependent): Old and new values as fields of a record data structure CREATE TRIGGER HistoryTable ON OriginalTable FOR INSERT, DELETE, UPDATE AS DECLARE @Now DATETIME SET @Now = GETDATE() / * deleting section */ UPDATE HistoryTable SET EndDate = @Now WHERE EndDate IS NULL AND Column1 = OLD.Column1 / * inserting section */ INSERT INTO HistoryTable (Column1, Column2, ...,Columnn, StartDate, EndDate) VALUES (NEW.Column1, NEW.Column2, ..., NEW.Columnn, @Now, NULL) Old and new values as rows of virtual tables CREATE TRIGGER HistoryTable ON OriginalTable FOR INSERT, DELETE, UPDATE AS DECLARE @Now DATETIME SET @Now = GETDATE() / * deleting section */ UPDATE HistoryTable SET EndDate = @Now FROM HistoryTable, DELETED WHERE HistoryTable.Column1 = DELETED.Column1 AND HistoryTable.EndDate IS NULL / * inserting section */ INSERT INTO HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate) SELECT (Column1, Column2, ..., Columnn, @Now, NULL) FROM INSERTED 抄文引用元・出典: フリー百科事典『 ウィキペディア（Wikipedia）』 ■ウィキペディアで「Log trigger」の詳細全文を読む スポンサード リンク
|